﻿Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Data.Common
Imports Excel = Microsoft.Office.Interop.Excel
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.IO
Imports Microsoft.Office.Interop
Imports System.Data

Public Class CExportExcelDAO

    Public Function ImporttableFromExcel(ByVal _sheetName As String, ByVal _filePath As String) As Boolean
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _filePath & " ;Extended Properties='Excel 12.0 Xml;HDR=YES'"
            Using connection As New OleDb.OleDbConnection(excelConnectionString)
                Dim strcmd = "Select * from [" & _sheetName & "$]"
                Dim command As New OleDbCommand(strcmd, connection)

                connection.Open()

                ' Create DbDataReader to Data Worksheet
                Using dr As DbDataReader = command.ExecuteReader()
                    ' SQL Server Connection String
                    ' Bulk Copy to SQL Server
                    Using bulkCopy As New SqlBulkCopy(CDataProvider.connectonString)
                        bulkCopy.DestinationTableName = _sheetName
                        bulkCopy.WriteToServer(dr)
                    End Using
                End Using
            End Using
        End Using
        Return True
    End Function

    Public Function ExportTableToExcel(ByVal _path As String, ByVal tablename As String) As Boolean
        Dim Excel As Object = CreateObject("Excel.Application")
        If Excel Is Nothing Then
            Return False
        End If
        Excel.SheetsInNewWorkbook = 1
        Excel.Workbooks.Add()
        ExportTableToExcel(Excel, tablename, _path, 1)
        Excel.ActiveCell.Worksheet.SaveAs(_path)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
        Excel = Nothing
        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each i As Process In pro
            i.Kill()
        Next
        Return True
    End Function

    Public Function ExportAllTableToExcel(ByVal _path As String) As Boolean
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("uspSelectAllTables", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim lTables As New List(Of String)
            Using reader As IDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                While (reader.Read())
                    lTables.Add(reader("table_name"))
                End While
            End Using

            Dim Excel As Object = CreateObject("Excel.Application")
            If Excel Is Nothing Then
                Return False
            End If
            Excel.SheetsInNewWorkbook = lTables.Count
            Excel.Workbooks.Add()
            Dim vt As Integer = 1
            For Each Str As String In lTables
                ExportTableToExcel(Excel, Str, _path, vt)
                vt += 1
            Next
            Excel.ActiveCell.Worksheet.SaveAs(_path)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
            For Each i As Process In pro
                i.Kill()
            Next
            Return True
        End Using
    End Function

    Public Function ExportTableToExcel(ByRef Excel As Object, ByVal _tablename As String, ByVal _filePath As String, ByVal vitri As Integer) As Boolean

        Using source As SqlConnection = CDataProvider.Connect()
            Dim sql As String = String.Format("SELECT * FROM [{0}]", _tablename)

            Dim command As New SqlCommand(sql, source)
            Dim adapter As New SqlDataAdapter(command)
            Dim dt As New System.Data.DataTable
            adapter.Fill(dt)
            ExportDataTableToExcel(Excel, dt, _tablename, _filePath, vitri)

            Return True
        End Using

    End Function

    Private Function ExportDataTableToExcel(ByRef Excel As Object, ByVal objDT As DataTable, ByVal strFilename As String, ByVal strPath As String, ByVal vitri As Integer) As Boolean
        Dim intCol, intRow As Integer
        Try
            With Excel

                .Worksheets(vitri).Select()
                .Worksheets(vitri).Name = strFilename

                Dim intI As Integer = 1
                For intCol = 0 To objDT.Columns.Count - 1
                    .cells(1, intI).value = objDT.Columns(intCol).ColumnName
                    .cells(1, intI).EntireRow.Font.Bold = True
                    intI += 1
                Next
                Dim intK As Integer = 1
                For intCol = 0 To objDT.Columns.Count - 1
                    intI = 2
                    For intRow = 0 To objDT.Rows.Count - 1
                        .Cells(intI, intK).Value = objDT.Rows(intRow).ItemArray(intCol)
                        intI += 1
                    Next
                    intK += 1
                Next
                ' .ActiveCell.Worksheet.SaveAs(strPath)
            End With

        Catch ex As Exception
            Return False
        End Try
        ' The excel is created and opened for insert value. We most close this excel using this system
        Return True
    End Function




End Class

